package com.xiyi.shouxin.dao.user;

import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Repository;

import com.xiyi.shouxin.dao.BaseRepository;
import com.xiyi.shouxin.dao.JdbcPreparedStatement;
import com.xiyi.shouxin.entity.user.Practice;
import com.xiyi.shouxin.entity.user.RankData;
import com.xiyi.shouxin.entity.user.mapper.PracticeMapper;
import com.xiyi.shouxin.entity.user.mapper.RankDataMapper;
import com.xiyi.shouxin.exception.CommonException;
import com.xiyi.shouxin.exception.ErrorCode;

@Repository
public class PracticeRepository extends BaseRepository<Practice> {

    public PracticeRepository() {
        super();
        super.stat = new JdbcPreparedStatement<>(Practice.class);
    }

    public Integer countRankById(Long uid)
            throws CommonException {
        if (uid == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder(
                "SELECT NUM FROM (SELECT t.uid as uid,rank() over(ORDER BY t.qq DESC) as NUM FROM  (SELECT ");
        sb.append(stat.GetColumnName("uid"));
        sb.append(" AS uid,MAX(");
        sb.append(stat.GetColumnName("qualifiedQuantity"));
        sb.append(") AS qq FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("isDelete"));
        sb.append(" != 1 ");
        sb.append(" GROUP BY ");
        sb.append(stat.GetColumnName("uid"));
        sb.append(" ) AS t)AS r");
        sb.append(" WHERE r.uid = ? LIMIT 1");
        try {
            Integer rank = template.queryForObject(sb.toString(), Integer.class, uid);
            return rank;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0;
    }

    public Integer countRankInTeamById(Long uid, List<Long> uids)
            throws CommonException {
        if (uid == null || uids == null || uids.isEmpty()) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder(
                "SELECT NUM FROM (SELECT t.uid as uid,rank() over(ORDER BY t.qq DESC) as NUM FROM  (SELECT ");
        sb.append(stat.GetColumnName("uid"));
        sb.append(" AS uid,MAX(");
        sb.append(stat.GetColumnName("qualifiedQuantity"));
        sb.append(") AS qq FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("isDelete"));
        sb.append(" != 1 AND ");
        sb.append(stat.GetColumnName("uid"));
        sb.append(" IN ( ");
        List<String> els = new ArrayList<>();
        uids.forEach(id -> els.add("?"));
        sb.append(String.join(",", els));
        sb.append(") GROUP BY ");
        sb.append(stat.GetColumnName("uid"));
        sb.append(" ) AS t)AS r");
        sb.append(" WHERE r.uid = ? LIMIT 1");
        uids.add(uid);
        try {
            Integer rank = template.queryForObject(sb.toString(), Integer.class, uids.toArray());
            return rank;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0;
    }

    public List<Practice> findByMultiConditionsByPage(List<Long> uids, List<Long> pids, Long adminId, Long piciId,
            Date startTime, Date endTime,
            Sort sort,
            int page, int size) throws Exception {
        List<Object> args = new ArrayList<>();
        List<String> conditions = new ArrayList<>();
        if (uids != null && !uids.isEmpty()) {
            StringBuilder sb = new StringBuilder(stat.GetColumnName("uid"));
            sb.append(" IN ( ");
            List<String> els = new ArrayList<>();
            uids.forEach(id -> els.add("?"));
            sb.append(String.join(",", els));
            sb.append(" )");
            conditions.add(sb.toString());
            args.addAll(uids);
        }
        if (pids != null && !pids.isEmpty()) {
            StringBuilder sb = new StringBuilder(stat.GetColumnName("id"));
            sb.append(" IN ( ");
            List<String> els = new ArrayList<>();
            pids.forEach(id -> els.add("?"));
            sb.append(String.join(",", els));
            sb.append(" )");
            conditions.add(sb.toString());
            args.addAll(pids);
        }
        if (adminId != null) {
            StringBuilder sb = new StringBuilder(stat.GetColumnName("adminId"));
            sb.append(" IS NULL OR ");
            sb.append(stat.GetColumnName("adminId"));
            sb.append(" = ? ");
            conditions.add(sb.toString());
            args.add(adminId);
        }
        if (piciId != null) {
            StringBuilder sb = new StringBuilder(stat.GetColumnName("piciId"));
            sb.append(" = ? ");
            conditions.add(sb.toString());
            args.add(piciId);
        }
        if (startTime != null) {
            conditions.add("CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) >= ? ");
            args.add(startTime.getTime() / 1000);
        }
        if (endTime != null) {
            conditions.add("CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) <= ? ");
            args.add(endTime.getTime() / 1000);
        }
        conditions.add(stat.GetColumnName("isDelete") + " != 1 ");
        conditions.add(stat.GetColumnName("time") + " >= 30 ");
        return findByMultiConditionsByPage(conditions, args, sort, page, size, new PracticeMapper());
    }

    public Integer countByMultiConditions(List<Long> uids, List<Long> pids, Long adminId, Long piciId, Date startTime,
            Date endTime) {
        List<Object> args = new ArrayList<>();
        List<String> conditions = new ArrayList<>();
        if (uids != null && !uids.isEmpty()) {
            StringBuilder sb = new StringBuilder(stat.GetColumnName("uid"));
            sb.append(" IN ( ");
            List<String> els = new ArrayList<>();
            uids.forEach(id -> els.add("?"));
            sb.append(String.join(",", els));
            sb.append(" )");
            conditions.add(sb.toString());
            args.addAll(uids);
        }
        if (pids != null && !pids.isEmpty()) {
            StringBuilder sb = new StringBuilder(stat.GetColumnName("id"));
            sb.append(" IN ( ");
            List<String> els = new ArrayList<>();
            pids.forEach(id -> els.add("?"));
            sb.append(String.join(",", els));
            sb.append(" )");
            conditions.add(sb.toString());
            args.addAll(pids);
        }
        if (adminId != null) {
            StringBuilder sb = new StringBuilder(stat.GetColumnName("adminId"));
            sb.append(" IS NULL OR ");
            sb.append(stat.GetColumnName("adminId"));
            sb.append(" = ? ");
            conditions.add(sb.toString());
            args.add(adminId);
        }
        if (piciId != null) {
            StringBuilder sb = new StringBuilder(stat.GetColumnName("piciId"));
            sb.append(" = ? ");
            conditions.add(sb.toString());
            args.add(piciId);
        }
        if (startTime != null) {
            conditions.add("CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) >= ? ");
            args.add(startTime.getTime() / 1000);
        }
        if (endTime != null) {
            conditions.add("CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) <= ? ");
            args.add(endTime.getTime() / 1000);
        }
        conditions.add(stat.GetColumnName("isDelete") + " != 1 ");
        conditions.add(stat.GetColumnName("time") + " >= 30 ");
        return countByMultiConditions(conditions, args);
    }

    public Integer sumTotalByUid(Long uid) throws CommonException {
        if (uid == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("SELECT SUM(");
        sb.append(stat.GetColumnName("total"));
        sb.append(") FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("uid"));
        sb.append(" = ? AND ");
        sb.append(stat.GetColumnName("isDelete"));
        sb.append(" != 1 ");
        try {
            Integer total = template.queryForObject(sb.toString(), Integer.class, uid);
            return total;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0;
    }

    public Long sumTimeByUid(Long uid) throws CommonException {
        if (uid == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("SELECT SUM(");
        sb.append(stat.GetColumnName("time"));
        sb.append(") FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("uid"));
        sb.append(" = ? AND ");
        sb.append(stat.GetColumnName("isDelete"));
        sb.append(" != 1 ");
        try {
            Long time = template.queryForObject(sb.toString(), Long.class, uid);
            return time;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0L;
    }

    public Long sumTimeByUidAndTime(Long uid, Date startTime, Date endTime) throws CommonException {
        if (uid == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("SELECT SUM(");
        sb.append(stat.GetColumnName("time"));
        sb.append(") FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("isDelete"));
        sb.append(" != 1 ");
        List<Object> args = new ArrayList<>();
        if (uid != null) {
            sb.append(" AND ");
            sb.append(stat.GetColumnName("uid"));
            sb.append(" = ? ");
            args.add(uid);
        }
        if (startTime != null) {
            sb.append(" AND CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) >= ? ");
            args.add(startTime.getTime() / 1000);
        }
        if (endTime != null) {
            sb.append(" AND CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) <= ? ");
            args.add(endTime.getTime() / 1000);
        }

        try {
            Long time = template.queryForObject(sb.toString(), Long.class, args.toArray());
            return time;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0L;
    }

    public void addNotrest(Long id) throws Exception {
        if (id == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("UPDATE ");
        sb.append(stat.GetTableName());
        sb.append(" SET ");
        sb.append(stat.GetColumnName("notReset"));
        sb.append(" = ");
        sb.append(stat.GetColumnName("notReset"));
        sb.append(" + 1");
        sb.append(" WHERE id = ?");
        template.update(sb.toString(), id);
    }

    public Long countTotal(List<Long> uids) {
        List<Object> args = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SUM(");
        sb.append(stat.GetColumnName("total"));
        sb.append(") FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("isDelete"));
        sb.append(" != 1 ");
        if (uids != null && !uids.isEmpty()) {
            sb.append(" AND ");
            sb.append(stat.GetColumnName("uid"));
            sb.append(" IN ( ");
            List<String> els = new ArrayList<>();
            uids.forEach(id -> els.add("?"));
            sb.append(String.join(",", els));
            sb.append(" )");
            args.addAll(uids);
        }
        try {
            Long total = template.queryForObject(sb.toString(), Long.class, args.toArray());
            return total;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0L;
    }

    public Long countQualified(List<Long> uids) {
        List<Object> args = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT SUM(");
        sb.append(stat.GetColumnName("qualifiedQuantity"));
        sb.append(") FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("isDelete"));
        sb.append(" != 1 ");
        if (uids != null && !uids.isEmpty()) {
            sb.append(" AND ");
            sb.append(stat.GetColumnName("uid"));
            sb.append(" IN ( ");
            List<String> els = new ArrayList<>();
            uids.forEach(id -> els.add("?"));
            sb.append(String.join(",", els));
            sb.append(" )");
            args.addAll(uids);
        }
        try {
            Long total = template.queryForObject(sb.toString(), Long.class, args.toArray());
            return total;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0L;
    }

    public Long countNumByPiciId(Long piciId) throws CommonException {
        if (piciId == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        List<Object> args = new ArrayList<>();
        StringBuilder sb = new StringBuilder("SELECT COUNT(1) FROM");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("piciId"));
        sb.append(" = ? ");
        args.add(piciId);
        try {
            Long total = template.queryForObject(sb.toString(), Long.class, args.toArray());
            return total;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0L;
    }

    public Long countUserNumByTime(List<Long> uids, Date startTime, Date endTime) throws CommonException {
        StringBuilder sb = new StringBuilder("SELECT COUNT(DISTINCT ");
        sb.append(stat.GetColumnName("uid"));
        sb.append(") FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("isDelete"));
        sb.append(" != 1 ");
        List<Object> args = new ArrayList<>();
        if (startTime != null) {
            sb.append(" AND CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) >= ? ");
            args.add(startTime.getTime() / 1000);
        }
        if (endTime != null) {
            sb.append(" AND CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) <= ? ");
            args.add(endTime.getTime() / 1000);
        }
        if (uids != null && !uids.isEmpty()) {
            sb.append(" AND ");
            sb.append(stat.GetColumnName("uid"));
            sb.append(" IN ( ");
            List<String> els = new ArrayList<>();
            uids.forEach(id -> els.add("?"));
            sb.append(String.join(",", els));
            sb.append(" )");
            args.addAll(uids);
        }
        try {

            Long total = template.queryForObject(sb.toString(), Long.class, args.toArray());
            return total;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0L;
    }

    public Long sumUserNumByTime(Long userId, Date startTime, Date endTime) throws CommonException {
        if (userId == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("SELECT COUNT(1) FROM");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("isDelete"));
        sb.append(" != 1 AND ");
        sb.append(stat.GetColumnName("uid"));
        sb.append(" = ? ");
        List<Object> args = new ArrayList<>();
        args.add(userId);
        if (startTime != null) {
            sb.append(" AND CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) >= ? ");
            args.add(startTime.getTime() / 1000);
        }
        if (endTime != null) {
            sb.append(" AND CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) <= ? ");
            args.add(endTime.getTime() / 1000);
        }
        try {

            Long total = template.queryForObject(sb.toString(), Long.class, args.toArray());
            return total;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0L;
    }

    public Long countNumByTime(List<Long> uids, Date startTime, Date endTime) throws CommonException {
        StringBuilder sb = new StringBuilder("SELECT SUM(");
        sb.append(stat.GetColumnName("total"));
        sb.append(") FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("isDelete"));
        sb.append(" != 1 ");
        List<Object> args = new ArrayList<>();
        if (startTime != null) {
            sb.append(" AND CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) >= ? ");
            args.add(startTime.getTime() / 1000);
        }
        if (endTime != null) {
            sb.append(" AND CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) <= ? ");
            args.add(endTime.getTime() / 1000);
        }
        if (uids != null && !uids.isEmpty()) {
            sb.append(" AND ");
            sb.append(stat.GetColumnName("uid"));
            sb.append(" IN ( ");
            List<String> els = new ArrayList<>();
            uids.forEach(id -> els.add("?"));
            sb.append(String.join(",", els));
            sb.append(" )");
            args.addAll(uids);
        }
        try {
            Long total = template.queryForObject(sb.toString(), Long.class, args.toArray());
            return total;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0L;
    }

    public List<Long> findAllNoTimePracticeByUid(Long uid, Long limitTime) throws CommonException {
        if (uid == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("SELECT ");
        sb.append(stat.GetColumnName("id"));
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("uid"));
        sb.append(" = ? AND ");
        sb.append(stat.GetColumnName("time"));
        sb.append(" < ? ");
        try {
            List<Long> list = template.queryForList(sb.toString(), Long.class, uid, limitTime);
            return list;
        } catch (EmptyResultDataAccessException e) {

        }
        return new ArrayList<>();
    }

    public Practice findByUserIdAndPiciId(Long userId, Long piciId) throws CommonException {
        if (userId == null || piciId == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("SELECT ");
        sb.append(stat.GetQueryColumns());
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("uid"));
        sb.append(" = ? AND ");
        sb.append(stat.GetColumnName("piciId"));
        sb.append(" = ? ORDER BY ");
        sb.append(stat.GetColumnName("createdAt"));
        sb.append(" DESC LIMIT 1");
        try {
            Practice practice = template.queryForObject(sb.toString(), new Object[] { userId, piciId },
                    new PracticeMapper());
            return practice;
        } catch (EmptyResultDataAccessException e) {

        }
        return null;
    }

    public Practice findFirstByUserIdSort(Long userId, Date startTime, Date endTime, Sort sort) throws CommonException {
        if (userId == null || sort == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }
        StringBuilder sb = new StringBuilder("SELECT ");
        sb.append(stat.GetQueryColumns());
        sb.append(" FROM ");
        sb.append(stat.GetTableName());
        sb.append(" WHERE ");
        sb.append(stat.GetColumnName("isDelete"));
        sb.append(" != 1 AND ");
        sb.append(stat.GetColumnName("uid"));
        sb.append(" = ? ");
        List<Object> args = new ArrayList<>();
        args.add(userId);
        if (startTime != null) {
            sb.append(" AND CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) >= ? ");
            args.add(startTime.getTime() / 1000);
        }
        if (endTime != null) {
            sb.append(" AND CAST(" + stat.GetColumnName("createdAt") + " AS SIGNED) <= ? ");
            args.add(endTime.getTime() / 1000);
        }
        sb.append(" ORDER BY ");
        sb.append(sort.toString().replaceAll(":", " "));
        sb.append(" LIMIT 1");
        try {
            Practice practice = template.queryForObject(sb.toString(), args.toArray(),
                    new PracticeMapper());
            return practice;
        } catch (EmptyResultDataAccessException e) {

        }
        return null;
    }

    public Integer countContinuousNum(Long userId, Date startTime, Date endTime) throws CommonException {
        if (userId == null) {
            throw new CommonException(ErrorCode.INVALIDPARAMTER);
        }

        StringBuilder sb = new StringBuilder(
                "SELECT COUNT(*) as num_days FROM( SELECT *,date_sub(days,interval rn DAY) as results FROM ( SELECT days,row_number() over (ORDER BY days) as rn FROM( SELECT DISTINCT DATE_FORMAT(FROM_UNIXTIME(created_at), '%Y-%m-%d') AS days FROM practice WHERE uid = ? AND CAST(created_at AS SIGNED) >= ? AND CAST(created_at AS SIGNED) <= ? )t1 )t2 ) t3 GROUP BY results ORDER BY num_days DESC LIMIT 1");
        try {
            Integer total = template.queryForObject(sb.toString(), Integer.class,
                    new Object[] { userId, startTime.getTime() / 1000, endTime.getTime() / 1000 });
            return total;
        } catch (EmptyResultDataAccessException e) {

        }
        return 0;
    }

    public List<RankData> sumUserGroup(Long adminId, Date startTime, Date endTime) throws CommonException {
        List<Object> args = new ArrayList<>();
        StringBuilder sb = new StringBuilder(
                "SELECT uid,SUM(qualified_quantity) AS qualified_quantity,SUM(total) AS total,MAX(qualified_quantity) AS max_total,MAX(pass_rate) AS pass_rate FROM practice WHERE is_delete = 0 AND time > 118 ");
        if (adminId != null) {
            sb.append(" AND admin_id = ? ");
            args.add(adminId);
        }

        sb.append(" AND CAST(created_at AS SIGNED) >= ? AND CAST(created_at AS SIGNED) <= ? GROUP BY uid");
        args.add(startTime.getTime() / 1000);
        args.add(endTime.getTime() / 1000);
        return template.query(sb.toString(), args.toArray(), new RankDataMapper());
    }
}
